#!/usr/bin/env python
# coding:utf-8
'''
DMZ各地区在线统计
'''
import sys,common.db
from logging import *
from common.date_time import *


def IPToInt(ipaddr):
    sectionList = ipaddr.split(".")
    return ((int)(sectionList[0]) << 24) + ((int)(sectionList[1]) << 16) +((int)(sectionList[2]) << 8) +(int)(sectionList[3])

def GetCityByIP(cur,ipaddr):
    ip = IPToInt(ipaddr)
    strSql = 'select city from dmz_resource.ip_lib where ip1 < %d and ip2 > %d'%(ip,ip)
    cur.execute(strSql)
    rs = cur.fetchone()
    if rs is not None:
        return rs[0]
    return 0


def CityOnlineRealTime(strCurDay,strCurTime):
    log(INFO,'StatDay : '+ strCurDay)
    strDay = strCurDay.replace('-','')

    # 连接数据库
    cnnBill = common.db.connect('billDB')
    cnnWeb = common.db.connect('DMZwebDB')
    curBill = cnnBill.cursor()
    curWeb = cnnWeb.cursor() 
    
    # 清除当前分钟已有数据
    strSql = 'delete from statisticdb.DMZCityOnlineRealTime where `DateTime` between \'%s %s\' and \'%s %s:59\''%(strCurDay,strCurTime,strCurDay,strCurTime[0:5])
    log(INFO, strSql)
    rowsDeleted = curBill.execute(strSql)
    cnnBill.commit()
    log(INFO,'Clear current minute city login success!(DeletedCount=%d)', rowsDeleted)

    #查询每分钟在线记录
    strSql = 'select zoneid, ipaddr, count(*) from statisticdb.DMZCityOnlineTemp group by zoneid, ipaddr order by zoneid'
    curBill.execute(strSql)
    counter = 0
    city_data = {}
    rs = curBill.fetchone()
    while rs is not None:
        zoneid,ipaddr,count = rs
        city= GetCityByIP(curWeb,ipaddr)
        if not city_data.has_key(zoneid):
            city_data[zoneid]= {}

        if city_data[zoneid].has_key(city):
            city_data[zoneid][city] += count
        else:
            city_data[zoneid][city] = count

        counter += 1
        if (counter % 1000) == 0:
            log(INFO,'Query city online count progress!(zoneid=%d, cityCount=%d, counter=%d)'%(zoneid,len(city_data[zoneid]),counter))
        rs = curBill.fetchone()
    log(INFO,'Query city online count compeleted!(counter=%d)'%(counter))

    sql='select city,province from dmz_resource.ip_city';
    curWeb.execute(sql)
    city_province={}
    rs = curWeb.fetchone()
    while rs is not None:
        city,province=rs
        city_province[city]=province
        rs = curWeb.fetchone()

    #插入城市在线统计数据
    counter = 0
    for zoneid in city_data:
        cityList = city_data[zoneid]
        for city in cityList:
            if city_province.has_key(city):
                province=city_province[city]
            else:
                province='null'
            strSql = 'insert into statisticdb.DMZCityOnlineRealTime(`DateTime`,`ZoneID`,`City`,`Province`,`OnlineCount`) values("%s %s", %d, %d, "%s", %d)'%(strCurDay,strCurTime,zoneid,city,province,cityList[city])
            rowsCount = curBill.execute(strSql)
            counter += 1
            if (counter % 10) == 0:
                log(INFO,'Insert city online real time progress!(counter=%d)'%(counter))
    cnnBill.commit()
    log(INFO,'Insert city online real time success!')

    curBill.close()
    curWeb.close()
    cnnBill.close()
    cnnWeb.close()


if __name__ != "__main__":
    
    # 检验输入参数,如果参数为空,返回昨天日期
    strNowTime = now()
    strCurDay = strNowTime[0:10]
    strCurTime = strNowTime[11:17]+'00'
    CityOnlineRealTime(strCurDay,strCurTime) 
    
